﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.CellType;
using Spread.Common.DataStore;
using Spread.Common.DataStore.DataSourceEntities;
using Spread.Common.DataStore.Model;

namespace GrapeCity.CostOfGoodsSold
{
    public partial class CostOfGoodsControl : Spread.Common.Features.Sample.BaseSampleControl
    {
        #region Fields
        private COGSDataSource _dataSource;
        private List<string> _costOfSalesCategories;
        private List<string> _revenueCategories;
        private List<string> _expenses;
        private DateTime _date;
        #endregion

        #region ctor
        public CostOfGoodsControl()
        {
            InitializeComponent();
            _dataSource = new DataService().COGSDataSource;

            _costOfSalesCategories = new List<string>();
            _revenueCategories = new List<string>();
            for (int i = 1; i <= 7; ++i)
            {
                _costOfSalesCategories.Add($"Category {i}");
                _revenueCategories.Add($"Category {i}");
            }

            _expenses = new List<string>() { "Salary Expenses", "Payroll Expenses", "Outside services",
                "Supplies (office and operating)", "Repairs and maintainance", "Advertising",
                "Car, delivery and travel", "Accounting and legal", "Rent", "Telephone", "Utilities", "Insurance",
                "Taxes (real estate, etc.)", "Interest", "Depreciation", "Other expenses (specify)",
                "Other expenses (specify)", "Other expenses (specify)", "Misc. (unspecified)" };

            Load += Form1_Load;
        }
        #endregion

        #region Event handlers
        private void Form1_Load(object sender, EventArgs e)
        {
            fpSpread.SuspendLayout();
            fpSpread.OpenExcel(@"Template\COGS.xlsx");

            fpSpread.ActiveSheet.Cells[1, 17].Tag = "FiscalDate";
            _date = new DateTime(DateTime.Now.Year - 1, 12, 31);
            fpSpread.ActiveSheet.Cells["FiscalDate"].Text = string.Format("{0:d}", _date);
            fpSpread.ActiveSheet.Cells["FiscalDate"].Locked = false;
            SetupSheet(fpSpread.ActiveSheet);
            LoadStats(fpSpread.ActiveSheet);

            fpSpread.ResumeLayout(true);
            fpSpread.Change += FpSpread_Change;
            fpSpread.EditChange += FpSpread_EditChange;
        }

        private void FpSpread_EditChange(object sender, EditorNotifyEventArgs e)
        {
            if (fpSpread.ActiveSheet.Cells[e.Row, e.Column].Tag?.ToString() == "FiscalDate")
            {
                fpSpread.ActiveSheet.AutoCalculation = false;
            }
        }

        private void FpSpread_Change(object sender, ChangeEventArgs e)
        {
            SheetView sheet = fpSpread.ActiveSheet;
            if (sheet.Cells[e.Row, e.Column].Tag?.ToString() == "FiscalDate")
            {
                DateTime date = DateTime.MaxValue;
                if (DateTime.TryParse(sheet.Cells["FiscalDate"].Text, out date) && date.Date < DateTime.Now.Date)
                {
                    fpSpread.SuspendLayout();
                    ClearCells(sheet);
                    _date = date;
                    LoadStats(sheet);
                    fpSpread.ResumeLayout(true);
                }
                else
                {
                    MessageBox.Show("Date must be valid and less than current date.", "Invalid Date",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                    fpSpread.Change -= FpSpread_Change;
                    sheet.Cells["FiscalDate"].Value = _date;
                    fpSpread.Change += FpSpread_Change;
                }
                sheet.AutoCalculation = true;
            }
        }
        #endregion

        #region Private methods
        private void SetupSheet(SheetView sheet)
        {
            for (int i = 1; i <= 7; ++i)
            {
                sheet.Rows[4 + i].Tag = $"Revenue_Category{i}";
                sheet.Rows[14 + i].Tag = $"CostOfSales_Category{i}";
            }
            sheet.Rows[27].Tag = "SalaryExpenses";
            sheet.Rows[28].Tag = "PayrollExpenses";
            sheet.Rows[29].Tag = "OutsideServices";
            sheet.Rows[30].Tag = "Supplies";
            sheet.Rows[31].Tag = "Repairs";
            sheet.Rows[32].Tag = "Advertising";
            sheet.Rows[33].Tag = "CarDelivery";
            sheet.Rows[34].Tag = "Accounting";
            sheet.Rows[35].Tag = "Rent";
            sheet.Rows[36].Tag = "Telephone";
            sheet.Rows[37].Tag = "Utilities";
            sheet.Rows[38].Tag = "Insurance";
            sheet.Rows[39].Tag = "Taxes";
            sheet.Rows[40].Tag = "Interest";
            sheet.Rows[41].Tag = "Depreciation";
            sheet.Rows[42].Tag = "OtherExpenses1";
            sheet.Rows[43].Tag = "OtherExpenses2";
            sheet.Rows[44].Tag = "OtherExpenses3";
            sheet.Rows[45].Tag = "Miscellaneous";

            SetCellTypes(sheet);
            SetLabels(sheet);
        }

        private void SetLabels(SheetView sheet)
        {
            for (int i = 0; i < 7; ++i)
            {
                sheet.Cells[5 + i, 1].Value = _revenueCategories[i];
                sheet.Cells[15 + i, 1].Value = _costOfSalesCategories[i];
            }

            for (int i = 0; i < _expenses.Count; ++i)
            {
                sheet.Cells[27 + i, 1].Value = _expenses[i];
            }
        }

        private void SetCellTypes(SheetView sheet)
        {
            fpSpread.SuspendLayout();
            CurrencyCellType currencyCell = new CurrencyCellType();
            currencyCell.DecimalPlaces = 0;

            int col = 3;
            for (int i = 0; i < 13; ++i)
            {
                sheet.Cells[5, col, 12, col].CellType = currencyCell;
                sheet.Cells[15, col, 22, col].CellType = currencyCell;
                sheet.Cells[24, col].CellType = currencyCell;
                sheet.Cells[27, col, 46, col].CellType = currencyCell;
                sheet.Cells[48, col].CellType = currencyCell;
                col += 2;
            }
            fpSpread.ResumeLayout(true);
        }

        private void ClearCells(SheetView sheet)
        {
            int col = 3;
            for (int i = 0; i < 12; ++i)
            {
                sheet.Cells[5, col, 11, col].ResetValue();
                sheet.Cells[15, col, 21, col].ResetValue();
                sheet.Cells[27, col, 45, col].ResetValue();
                col += 2;
            }
        }

        private void LoadStats(SheetView sheet)
        {
            for (int i = 0; i < 12; ++i)
            {
                int row = 5, col = 3 + 2 * i;

                try
                {
                    DateTime date;
                    object val = sheet.Cells[row - 2, col].Value;
                    if (val is double )
                        date = DateTime.FromOADate((double)val);
                    else if (val is DateTime )
                        date = (DateTime)val;
                    else
                        date = Convert.ToDateTime(sheet.Cells[row - 2, col].Text);
                    if (date.Date > DateTime.Now.Date)
                        return;
                    CostOfGoodsStats stats = _dataSource.GetCOGSStats(date, 7, 7);
                    foreach (int categorySales in stats.CategoryRevenues)
                        sheet.Cells[row++, col].Value = categorySales;

                    row += 3;
                    foreach (int costOfSales in stats.CategoryCosts)
                        sheet.Cells[row++, col].Value = costOfSales;

                    var properties = stats.Expenses.GetType().GetProperties(System.Reflection.BindingFlags.Public |
                        System.Reflection.BindingFlags.Instance).ToList();
                    foreach (var property in properties)
                    {
                        if (property.Name == "Others")
                            continue;
                        sheet.Cells[sheet.Rows[property.Name].Index, col].Value =
                            property.GetValue(stats.Expenses);
                    }

                    row = 42;
                    foreach (int otherExpense in stats.Expenses.Others)
                        sheet.Cells[row++, col].Value = otherExpense;
                }
                catch (InvalidCastException ex)
                {
                    System.Diagnostics.Debug.WriteLine(ex);
                    continue;
                }
            }
        }
        #endregion
    }
}


//
//CostOfGoodsControl.Designer.cs
//


namespace GrapeCity.CostOfGoodsSold
{
    partial class CostOfGoodsControl
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.fpSpread = new FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None);
            this.fpSpread_Sheet1 = new FarPoint.Win.Spread.SheetView();
            this.pnlMain.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread_Sheet1)).BeginInit();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this.fpSpread);
            // 
            // fpSpread
            // 
            this.fpSpread.AccessibleDescription = "";
            this.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill;
            this.fpSpread.Location = new System.Drawing.Point(0, 0);
            this.fpSpread.Name = "fpSpread";
            this.fpSpread.Sheets.AddRange(new FarPoint.Win.Spread.SheetView[] {
            this.fpSpread_Sheet1});
            this.fpSpread.Size = new System.Drawing.Size(477, 487);
            this.fpSpread.TabIndex = 0;
            // 
            // fpSpread_Sheet1
            // 
            this.fpSpread_Sheet1.Reset();
            this.fpSpread_Sheet1.SheetName = "Sheet1";
            // Formulas and custom names must be loaded with R1C1 reference style
            this.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this.fpSpread_Sheet1.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.SheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // CostOfGoodsControl
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "CostOfGoodsControl";
            this.pnlMain.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread_Sheet1)).EndInit();
            this.ResumeLayout(false);

        }

        #endregion

        private FarPoint.Win.Spread.FpSpread fpSpread;
        private FarPoint.Win.Spread.SheetView fpSpread_Sheet1;
    }
}
